package org.study.poi_excel.utils;

import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.study.poi_excel.annotation.Excel;
import org.study.poi_excel.excel.ExcelParam;
import org.study.poi_excel.style.CommonExcelStyle;



/**
 * @author ou_qu_sheng
 * Description:
 * 通用poi操作excel类，没有提供直接导入，只提供两种创建excel的方法（即workbook）。
 * 第一种在pojo类中使用注解Excel在字段field上进行注解配置。<br>
 * 第二种方法的前提是collection的类型为List<Object[]>，并且通过ExcelParam数组来指定各个列表头的类型。<br>
 * 注意：使用第二种createWorkBook创建列的顺序和ExcelParam数组顺序无关，与ExcelParam的index值有关
 */
@SuppressWarnings({ "rawtypes", "unchecked" })
public class ExcelUtils {
	private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);
	public static ExcelUtils instance = new ExcelUtils();

	private ExcelUtils() {
	}
	//------------------------------------创建workbook---------------------------------
	/**
	 * 创建一个workbook
	 * @param dataList  数据来源
	 * @param pojo    对应的pojo类型，也就是datalist的集合类型
	 * @return workbook
	 */
	public <T> Workbook createWorkBook(Collection<T> dataList, Class<T> pojo) {
		Workbook workbook = new SXSSFWorkbook();
		Map<ExcelParam, Field> fieldByClass = getFieldByClass(pojo);
		createSheetByMap(workbook, fieldByClass, dataList);
		return workbook;
	}
	
	/**
	 * 根据集合和Excel参数数据ExcelParam 来创建一个workbook
	 * @param dataList
	 * @param params
	 * @return
	 */
	public Workbook createWorkBook(Collection dataList, ExcelParam[] params) {
		Workbook workbook = new SXSSFWorkbook();
		createSheet(workbook, dataList, params);
		return workbook;
	}

	//------------------------------------------------------------------------------
	
	/**
	 * 获取有注解类型的field
	 * 
	 * @param cls
	 */
	public <T> Map<ExcelParam, Field> getFieldByClass(Class<T> cls) {
		Field[] fields = null;
		Class type = cls;
		ExcelParam param = null;
		Field field = null;
		String dateFormat = null;
		String name = null;
		Excel annotation = null;
		Map<ExcelParam, Field> map = new HashMap<ExcelParam, Field>(32);
		do {
			fields = type.getDeclaredFields();
			for (int i = 0, len = fields.length; i < len; i++) {
				field = fields[i];
				annotation = field.getAnnotation(Excel.class);
				if (null != annotation) {
					name = annotation.name();
					int index = annotation.index();
					dateFormat = annotation.dateFormat();
					double hight = annotation.hight();
					double width = annotation.width();
					param = new ExcelParam(name, dateFormat, index, width, hight);
					if(!map.containsKey(param)) {
						map.put(param, field);
					}
				}
			}
			// 遍历父类
			type = type.getSuperclass();
		} while (null != type && type != Object.class);
		return map;
	}

	//-----------------------------------------创建sheet------------------------------------
	/**
	 * 
	 * @param workbook
	 * @param map
	 * @param dataList
	 */
	public <T> void createSheetByMap(Workbook workbook, Map<ExcelParam, Field> map, Collection<T> dataList) {
		Sheet sheet = null;
				int size = dataList.size();
		int index = size/Contants.DEFAULT_SHEET_SIZE;
		int sub = size % Contants.DEFAULT_SHEET_SIZE;
		List<T> temp = null;
		List<T> subTemp = null;
		if(dataList instanceof ArrayList) {
			for(int i = 0; i<index; i++) {
				temp = (ArrayList<T>) dataList;
				subTemp = temp.subList(i*Contants.DEFAULT_SHEET_SIZE, (i+1)*Contants.DEFAULT_SHEET_SIZE);
				sheet = workbook.createSheet();
				workbook.setSheetName(i, "sheet"+i);
				createTitle(sheet, map, workbook);
				Iterator<T> iterator = subTemp.iterator();
				int rowIndex = 1;
				T value = null;
				while (iterator.hasNext()) {
					value = iterator.next();
					createRow(sheet, rowIndex++, map, value);
				}
			}
			if(sub >= 0) {
				temp = (ArrayList<T>) dataList;
				subTemp = temp.subList(size - sub, size);
				sheet = workbook.createSheet();
				workbook.setSheetName(index, "sheet"+index);
				createTitle(sheet, map, workbook);
				Iterator<T> iterator = subTemp.iterator();
				int rowIndex = 1;
				T value = null;
				while (iterator.hasNext()) {
					value = iterator.next();
					createRow(sheet, rowIndex++, map, value);
				}
			}
		}
		
	}
	
	/**
	 * 创建一个
	 * @param workbook
	 * @param dataList
	 * @param params
	 */
	public <T> void createSheet(Workbook workbook, Collection<T> dataList, ExcelParam[] params) {
		Sheet sheet = null;
		int size = dataList.size();
		int index = size / Contants.DEFAULT_SHEET_SIZE;
		int sub = size % Contants.DEFAULT_SHEET_SIZE;
		List temp = null;
		List subTemp = null;
		if(dataList instanceof ArrayList) {
			for(int i=0; i<index; i++) {
				temp = (ArrayList)dataList;
				subTemp = temp.subList(i*Contants.DEFAULT_SHEET_SIZE, (i+1)*Contants.DEFAULT_SHEET_SIZE);
				sheet = workbook.createSheet();
				workbook.setSheetName(i, "sheet"+i);
				createTitle(sheet, params, workbook);
				Iterator iterator = subTemp.iterator();
				int rowIndex = 1;
				Object next = null;
				Object[] values = null;
				while(iterator.hasNext()) {
					next = iterator.next();
					if(next instanceof Object[]) {
						values = (Object[]) next;
						createRow(sheet, rowIndex++, params, values);
					}
				}
			}
			if(sub >= 0) {
				temp = (ArrayList)dataList;
				subTemp = temp.subList(size-sub, size);
				sheet = workbook.createSheet();
				workbook.setSheetName(index, "sheet"+index);
				createTitle(sheet, params, workbook);
				Iterator iterator = subTemp.iterator();
				int rowIndex = 1;
				Object next = null;
				Object[] values = null;
				while(iterator.hasNext()) {
					next = iterator.next();
					if(next instanceof Object[]) {
						values = (Object[]) next;
						createRow(sheet, rowIndex++, params, values);
					}
				}
			}
		}
	}
	
	//--------------------------------------创建列标题--------------------------------------------------
	public void createTitle(Sheet sheet, ExcelParam[] params, Workbook workbook) {
		Row row = sheet.createRow(0);
		row.setHeight((short)350);
		ExcelParam param;
		Cell cell;
		for(int i = 0, len = params.length; i < len; i++) {
			param = params[i];
			sheet.setColumnWidth(param.getIndex(), (int)param.getWidth()*Contants.TWIPS_PER_PIEXL);
			cell = createStringCell(row, param.getIndex(), param.getName());
			cell.setCellStyle(CommonExcelStyle.createTitleStyle(workbook));
		}
	}

	/**
	 * 设置excel的列标题，标题是从注解的pojo类中的name字段
	 * 
	 * @param sheet
	 * @param map
	 *            封装参数的map
	 */
	public void createTitle(Sheet sheet, Map<ExcelParam, Field> map, Workbook workbook) {
		Row row = sheet.createRow(0);
		row.setHeight((short)350);
		Set<ExcelParam> keySet = map.keySet();
		Iterator<ExcelParam> iterator = keySet.iterator();
		ExcelParam param = null;
		Cell cell = null;
		while (iterator.hasNext()) {
			param = iterator.next();
			sheet.setColumnWidth(param.getIndex(), (int)param.getWidth()*Contants.TWIPS_PER_PIEXL);
			cell = row.createCell(param.getIndex());
			cell.setCellValue(param.getName());
			cell.setCellStyle(CommonExcelStyle.createTitleStyle(workbook));
		}
	}
	//-----------------------------------------------------------------------------------
	
	/**
	 * 获得target的字段field的值
	 * 根据不同字段的类型对一个excel单元格进行赋值操作，取值的对象为空时，跳过赋值操作
	 */
	public void setCellValue(Field field, Object target, ExcelParam param, Row row) throws Exception {
		Type type = field.getGenericType();
		String name = type.toString();
		int index = param.getIndex();
		field.setAccessible(true);
		switch (name) {
			case "int": {
				int value = field.getInt(target);
				row.createCell(index).setCellValue(value);
				break;
			}
			case "boolean": {
				boolean value = field.getBoolean(target);
				row.createCell(index).setCellValue(value);
				break;
			}
			case "double": {
				double value = field.getDouble(target);
				row.createCell(index).setCellValue(value);
				break;
			}
			case "float": {
				float value = field.getFloat(target);
				row.createCell(index).setCellValue(value);
				break;
			}
			case "char": {
				char value = field.getChar(target);
				createStringCell(row, index, value+"");
				break;
			}
			case "byte": {
				byte value = field.getByte(target);
				row.createCell(index).setCellValue(value);
				break;
			}
			case "short": {
				short value = field.getShort(target);
				row.createCell(index).setCellValue(value);
				break;
			}
			case "long": {
				long value = field.getLong(target);
				row.createCell(index).setCellValue(value);
				break;
			}
			case "class java.lang.String":{
				String value = (String)field.get(target);
				createStringCell(row, index, value);
				break;
			}
			case "class java.lang.Character":{
				Character value = (Character)field.get(target);
				if(null != value) {
					createStringCell(row, index, value.toString());
				}
				break;
			}
			case "class java.lang.Long":{
				Long value = (Long)field.get(target);
				if(null != value) {
					createDoubleCell(row, index,value.toString());
				}
				break;
			}
			case "class java.lang.Integer":{
				Integer value = (Integer) field.get(target);
				if(null != value) {
					createDoubleCell(row, index,value.toString());
				}
				break;
			}
			case "class java.lang.Short":{
				Short value = (Short) field.get(target);
				if(null != value) {
					createDoubleCell(row, index,value.toString());
				}
				break;
			}
			case "class java.lang.Boolean":{
				Boolean value = (Boolean)field.get(target);
				row.createCell(index).setCellValue(value);
				break;
			}
			case "class java.lang.Byte":{
				Byte value = (Byte) field.get(target);
				if(null != value) {
					createDoubleCell(row, index,value.toString());
				}
				break;
			}
			case "class java.lang.Double":{
				Double value = (Double) field.get(target);
				if(null != value) {
					createDoubleCell(row, index,value.toString());
				}
				break;
			}
			case "class java.lang.Float":{
				Float value = (Float) field.get(target);
				if(null != value) {
					createDoubleCell(row, index,value.toString());
				}
				break;
			}
			case "class java.util.Date":{
				Date date = (Date) field.get(target);
				if(null != date) {
					createDateCell(row, index, date, param.getDateFormat());
				}
				break;
			}
		}
	}

	/**
	 * 这是创建一行excel的单元格
	 * @param sheet 要创建数据的sheet
	 * @param index 创建row的index
	 * @param fieldMap 包含pojo类Excel注解的信息
	 * @param pojo类的一个实例，用于取出数据填充到excel 的单元格中
	 */
	public void createRow(Sheet sheet, int index, Map<ExcelParam, Field> fieldMap, Object target) {
		Row row = sheet.createRow(index);
		try {
			ExcelParam key = null;
			Field field = null;
			for (Map.Entry<ExcelParam, Field> entry : fieldMap.entrySet()) {
				key = entry.getKey();
				field = entry.getValue();
				setCellValue(field, target, key, row);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 创建一个excel的Row
	 * @param sheet  创建row的sheet
	 * @param index  创建row的index
	 * @param params 创建row的Excel的参数信息，比如cell的index（位置），cell的列宽（width）
	 * @param values 一个row对应一系列cell的值
	 */
	public void createRow(Sheet sheet, int index, ExcelParam[] params, Object[] values) {
		Row row = sheet.createRow(index);
		ExcelParam param;
		Object value;
		int paramLen = params.length;
		int valuesLen = values.length;
		if(paramLen < valuesLen) {
			log.warn("params 的长度和 values 的长度不等 {},{}",params.length,values.length);
		}
		if(paramLen > valuesLen) {
			throw new RuntimeException("params的长度比values的长度大，会出现数组越界的现象");
		}
		
		for(int i = 0, len = params.length; i < len; i++) {
			param = params[i];
			int _index = param.getIndex();
			value = values[_index];
			if(null != value) {
				if(value instanceof String) {
					createStringCell(row, _index, value.toString());
				} else if(value instanceof Double) {
					createDoubleCell(row, _index, value.toString());
				} else if(value instanceof Date) {
					createDateCell(row, _index, (Date)value, param.getDateFormat());
				} else if(value instanceof java.sql.Date) {
					createDateCell(row, _index, new Date(((java.sql.Date)value).getTime()), param.getDateFormat());
				} else if(value instanceof java.sql.Time) {
					createDateCell(row, _index, new Date(((java.sql.Time)value).getTime()), param.getDateFormat());
				} else if(value instanceof java.sql.Timestamp) {
					createDateCell(row, _index, new Date(((java.sql.Timestamp)value).getTime()), param.getDateFormat());
				} else if(value instanceof Long) {
					createDoubleCell(row, _index, value.toString());
				} else if(value instanceof Float) {
					createDoubleCell(row, _index,value.toString());
				} else if(value instanceof BigDecimal) {
					createDoubleCell(row, _index,value.toString());
				} else {
					createStringCell(row, _index, value.toString());
				}
			}
		}
	}
	
	/**
	 * 创建一个字符型cell
	 * @param row
	 * @param index
	 * @param strValue
	 */
	public Cell createStringCell(Row row, int index, String strValue) {
		Cell cell = row.createCell(index);
		cell.setCellValue(strValue);
		return cell;
	}
	
	/**
	 * 创建一个double数据类型的cell
	 * @param row
	 * @param index
	 * @param doubleValue
	 */
	public Cell createDoubleCell(Row row, int index, Object doubleValue) {
		Cell cell = row.createCell(index);
		if(null != doubleValue ) {
			cell.setCellValue(new BigDecimal(doubleValue.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
		}
		return cell;
	}
	
	/**
	 * 将date类型数据按指定的dateFormat转为字符串后插入excel
	 * @param row
	 * @param index
	 * @param date
	 * @param dateFormat
	 */
	public Cell createDateCell(Row row, int index, Date date, String dateFormat) {
		if(null == date) {
			return createStringCell(row, index, null);
		}
		SimpleDateFormat format;
		if(null == dateFormat) {
			format = new SimpleDateFormat(Contants.DEFAULT_DATE_FORMAT);
		} else {
			format = new SimpleDateFormat(dateFormat);
		}
		String dataStr = format.format(date);
		return createStringCell(row, index, dataStr);
	}
	
}
